package com.itheima.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @author: libin
 * @date: 2019/11/25 11:05
 * @version: 1.0
 * @describe:
 */
public class POITest {

    @Test
    public void write() {
//      1、右键新建Excel（xls=2003 xlsx=2003以后）      ----> new Excel对象
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        //new HSSFWorkbook()//老版xls=2003

//      2、默认创建三个Sheet                           -----> createSheet
        XSSFSheet sheet = xssfWorkbook.createSheet();
//      3、创建Row(行,从0开始)                         -----> createRow
        XSSFRow row = sheet.createRow(0);//第一行
//      4、创建Cell(列,从0开始)                        ----> createCell
//      5、填数据                                     ----> cell.setValue
        row.createCell(0).setCellValue("姓名");
        row.createCell(1).setCellValue("爱好");
        row.createCell(2).setCellValue("地址");


        XSSFRow row1 = sheet.createRow(1);//第二行
        row1.createCell(0).setCellValue("李滨");
        row1.createCell(1).setCellValue("敲代码");
        row1.createCell(2).setCellValue("上海");

        XSSFRow row2 = sheet.createRow(2);//第三行
        row2.createCell(0).setCellValue("弟弟");
        row2.createCell(1).setCellValue("吃零食");
        row2.createCell(2).setCellValue("常州");
//      6、点击保存                                   -----> 用FileOutPutStream写入文件
        try {
            FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\29786\\Desktop\\test.xlsx");
            //将表中数据写入fileOutputStream
            xssfWorkbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
            xssfWorkbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void read() {
//      1、双击打开excel             ---->new Excel对象("文件路径"
        try {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook("C:\\Users\\29786\\Desktop\\test.xlsx");

//      2、读取sheet                ----> getSheet("sheet名称或者下标")
            XSSFSheet sheetAt = xssfWorkbook.getSheetAt(0);
/*//      3、读取行 (从0开始)                  -----> getRow("下标")
            int lastRowNum = sheetAt.getLastRowNum();
            for (int i = 0; i <=lastRowNum ; i++) {
                XSSFRow row = sheetAt.getRow(i);
                int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                for (int j = 0; j <physicalNumberOfCells ; j++) {
//      4、读取cell （从1开始）                --->getCell("下标")
//      5、读取cell值               ---->getCellValue
                    System.out.print(row.getCell(j).getStringCellValue()+",");
                }
                System.out.println();
            }*/

//第二种方法读取内容
            //一次次嵌套循环
            for (Row cells : sheetAt) {
                for (Cell cell : cells) {
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue+",");
                }
                System.out.println();
            }
//      6、x 掉excel                --->关闭excel对象
            xssfWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
